Populate The Dictionary Tables
*After all necessary Dicriontionary tables have been created in the ODS database then, populate the dictionary tables by utilizing the SSIS tools.
Use the following SQL query code as your source to populate the Dictionary_Incident_Location table.
SELECT DISTINCT [CaseID],
[IncidentCity],
[IncidentState],
[IncidentDate],
[Latitude],
[Longitude]
FROM [dbo].[US_Mass_Shooting_1966_2019_view]
Use the following SQL query code as your source to populate the
Dictionary_Incident_Place table.
SELECT DISTINCT DIL.[LocationID],
MSV.[Title],
MSV.[IncidentPlaceType],
MSV.[RelationshipToIncidentLocation]
FROM [US_Mass_Shooting_ODS_DB].[dbo].[Dictionary_Incident_Location] AS DIL
LEFT JOIN [Cap_One_Project_Staging_DB].[dbo].[US_Mass_Shooting_1966_2019_view]AS
MSV
on DIL.[CaseID] = MSV.[CaseID]
Use the following SQL query code as your source to populate
the Dictionary_Shooter table.
SELECT DISTINCT
[CaseID]
,[ShooterAge]
,[ShooterSex]
,[ShooterRace]
FROM [Cap_One_Project_Staging_DB].[dbo].[US_Mass_Shooting_1966_2019_view]
Use the following SQL query code as your source to populate
the Dictionary_Shooter_History_Of_Mental_Illness table.
SELECT DISTINCT DS.[ShooterID]
,MSV.[HistoryOfMentalIllnessGeneral]
FROM [US_Mass_Shooter_ODS].[dbo].[Dictionary_Shooter]AS DS
LEFT JOIN [Cap_One_Project_Staging_DB].[dbo].[US_Mass_Shooting_1966_2019_view]AS
MSV
on DS.[CaseID]
= MSV.[CaseID]
Use the following SQL query code as your source to populate
the Dictionary_Shooter_Status table.
SELECT DISTINCT DS.[ShooterID]
,MSV.[ShooterCurrentStatus]
FROM [US_Mass_Shooter_ODS_DB].[dbo].[Dictionary_Shooter]AS DS
LEFT JOIN [CAP_Project_One_Staging].[dbo].[US_Mass_Shooting_1966_2019_view]AS
MSV
on DS.[CaseID] =
MSV.[CaseID]
Use the following SQL query code as your source to populate
the Dictionary_Shooters_Motive table.
SELECT DISTINCT
DS.[ShooterID]
,MSV.[PossibleMotiveGeneral]
FROM [US_Mass_Shooter_ODS_DB].[dbo].[Dictionary_Shooter]AS
DS
LEFT JOIN [Cap_One_Project_Staging_DB].[dbo].[US_Mass_Shooting_1966_2019_view]AS
MSV
on DS.[CaseID] =
MSV.[CaseID]
Use the following SQL query code as your source to populate the Dictionary_Trageted_Victim table.
SELECT DISTINCT CaseID,
[TargetedVictimGeneral],
[Description]
FROM [Cap_One_Project_Staging_DB].[dbo].[US_Mass_Shooting_1966_2019_view]
Use the following SQL query code as your source to populate the Dictionary_Weapons table.
Use the following SQL query code as your source to populate the tbl_Mass_Shooting_ODS table.
SELECT DISTINCT
FROM